﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DAL;
using System.Data.Objects;
using BusinessEntities.Model;
using System.Data.Entity.Infrastructure;
using System.Data.Objects.SqlClient;
using System.Xml.Serialization;
using System.IO;
using System.Data.SqlClient;
using System.Data;

namespace ConsoleTestProject
{
    class Program
    {
       // private static DalImpl dal = new DalImpl();
        static void Main(string[] args)
        {
         /*   Invite i = new Invite();
            i.AdditionalInvitedGuests = 0;
            i.EventID = 1;
            i.GuestID = 1;
            i.ReservedGuests = 0;
            i.RSVPd = false;
            dal.CreateInvite(i);*/
            EventsDB db = new EventsDB();
            //string keyword = "St";
          /*  var ret =  (from g in db.Guests
                    where g.UserID == 1
                    select SqlFunctions.PatIndex("%St%", g.LastName));
            
            foreach (var g in ret)
            {
                Console.WriteLine(g);
            }*/
            SaveGuests(db);
            //db.Invites.FirstOrDefault().Gift = new Check() { Amount = 100, DepositDate = DateTime.Now, Description = "Bla Bla", Currency = Currency.EUR };
            //db.SaveChanges();
           // Gift g = db.Invites.FirstOrDefault().Gift;
            //Type t = g.GetType();
            Console.WriteLine("------");
            Console.ReadKey();
        }

        static void SaveGuests(EventsDB db)
        {
            /*var guests = (from g in db.Guests
                         where g.UserID == (from u in db.Users
                                            where u.Email == "r.n.lipkin@gmail.com"
                                            select u.ID).FirstOrDefault()
                         select g).ToList();
            XmlRootAttribute r = new XmlRootAttribute("Guests");
            XmlSerializer s = new XmlSerializer(guests.GetType(), r);
            var f = File.OpenWrite(@"C:\users\raanan\desktop\guests.xml");
            s.Serialize(f, guests);
            f.Close();*/
            var query = @"SELECT 
      [MaleFirstName]
      ,[FemaleFirstName]
      ,[LastName]
      ,[Email]
      ,[Couple]
      ,[CityID]
      ,[Street]
      ,[HouseNumber]
      ,[ApartmentNumber]
      ,[UserID]
  FROM [DAL.EventsDB].[dbo].[Guests]";
            
            using (SqlConnection _con = new SqlConnection(@"Data Source=LIPKIN\SQLEXPRESS;database=DAL.EventsDB;integrated security=SSPI;"))
            using (SqlCommand _cmd = new SqlCommand(query, _con) { CommandType = System.Data.CommandType.Text })
            {
                SqlDataAdapter da = new SqlDataAdapter(_cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                ds.DataSetName = "Guests";
                ds.Tables[0].TableName = "Guest";
                ds.Tables[0].WriteXml(@"C:\users\raanan\desktop\test.xml");
                /*_con.Open();
                string result = _cmd.ExecuteScalar().ToString();
                _con.Close();*/
            
            }
        }
    }
}
